using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Windows.Forms;
using DirectoryEnumerator;

namespace OllieExplorer
{
    public enum Location
    {
        Top,
        Bottom,
        Clear
    };
    public partial class FastDBListBox : UserControl
    {
        private const string CONNECTION_STRING = "server=(local)\\SQLEXPRESS;user id=filebrowser;password=filebrowser;initial catalog=filesystem";
        private readonly SqlConnection _SqlConnection;
        public FastDBListBox()
        {
            InitializeComponent();            
            _SqlConnection = new SqlConnection(CONNECTION_STRING);
            _SqlConnection.Open();
            int? directoryId;
            using(var sqlCommand = new SqlCommand())
            {
                sqlCommand.Connection = _SqlConnection;
                sqlCommand.CommandText = "SELECT id FROM directory where directory = @directory";
                sqlCommand.Parameters.AddWithValue("@directory", "c:\\windows\\system32");
                directoryId = (int?)sqlCommand.ExecuteScalar();
                if(!directoryId.HasValue)
                {
                    using(var insertCommand = new SqlCommand())
                    {
                        insertCommand.Connection = _SqlConnection;
                        insertCommand.CommandText =
                            @"INSERT INTO directory (directory) VALUES (@directory);
                                                        SELECT scope_identity()";
                        insertCommand.Parameters.AddWithValue("@directory", "c:\\windows\\system32");
                        directoryId = Convert.ToInt32(insertCommand.ExecuteScalar());
                        
                    }
                }
            }
            
            using(var clearOldResults = new SqlCommand())
            {
                clearOldResults.Connection = _SqlConnection;
                clearOldResults.CommandText = "DELETE FROM directoryResults where resultId = @resultId";
                clearOldResults.Parameters.AddWithValue("@resultId", directoryId);
                clearOldResults.ExecuteNonQuery();
            }
            var directoryInfo = new DirectoryInfo("c:\\windows\\system32");
            directoryInfo.GetFileSystemInfos();
            var fileDirectoryEnumerable = new FileDirectoryEnumerable
                                              {
                                                  SearchPath = "c:\\windows\\system32",
                                                  SearchPattern = "*.*",
                                                  SearchForFile = true,
                                                  SearchForDirectory = true
                                              };


            var sqlTransaction = _SqlConnection.BeginTransaction();
            var sqlBulkCopyOptions = new SqlBulkCopyOptions();


            var sqlBulkCopy = new SqlBulkCopy(_SqlConnection, sqlBulkCopyOptions, sqlTransaction)
                                  {
                                      BatchSize = 200,
                                      DestinationTableName = "directoryResults"
                                  };
            sqlBulkCopy.ColumnMappings.Add("fileName", "fileName");
            sqlBulkCopy.ColumnMappings.Add("dateModified", "dateModified");
            sqlBulkCopy.ColumnMappings.Add("fileType", "fileType");
            sqlBulkCopy.ColumnMappings.Add("resultId", "resultId");
            var counter = 1;
            var dataTable = new DataTable();
            dataTable.Columns.Add("fileName");
            dataTable.Columns.Add("dateModified");
            dataTable.Columns.Add("fileType");
            dataTable.Columns.Add("resultId");
            foreach(FileSystemInfo fileSystemInfo in fileDirectoryEnumerable)
            {
                if (counter % 400 == 0)
                {
                    sqlBulkCopy.WriteToServer(dataTable);
                    dataTable = new DataTable();
                    counter = 0;
                    dataTable.Columns.Add("fileName");
                    dataTable.Columns.Add("dateModified");
                    dataTable.Columns.Add("fileType");
                    dataTable.Columns.Add("resultId");
                }                
                var dataRow = dataTable.Rows.Add();
                dataRow["fileName"] = fileSystemInfo.Name;
                dataRow["dateModified"] = fileSystemInfo.LastWriteTimeUtc;
                dataRow["fileType"] = Convert.ToInt32(fileSystemInfo.Attributes);
                dataRow["resultId"] = directoryId;
            }
            sqlBulkCopy.WriteToServer(dataTable);
            sqlTransaction.Commit();

#if true

            var gridColumn = new GridColumn {Width = 30, ColumnHeadding = "fileName"};

            gridControl1.Columns.Add(gridColumn);
            gridColumn = new GridColumn {Width = 30, ColumnHeadding = "dateModified"};
            gridControl1.Columns.Add(gridColumn);

            FillData(true, 0, OllieExplorer.Location.Bottom);
           
#else
            GridColumn gridColumn = new GridColumn();
            gridColumn.Width = 30;
            gridColumn.ColumnHeadding = "this is a test";
            gridColumn.Data.Add("Hello");
            gridControl1.Columns.Add(gridColumn);
            gridColumn = new GridColumn();
            gridColumn.Width = 30;
            gridColumn.ColumnHeadding = "this is another test";
            gridColumn.Data.Add("Goodbye");
            gridControl1.Columns.Add(gridColumn);
#endif
        }

        private void LoadNewData(GridControl sender, int startRow)
        {
            FillData(true, startRow, OllieExplorer.Location.Bottom);
        }

        private void LoadNewTopData(GridControl sender, int startRow)
        {
            FillData(true, startRow, OllieExplorer.Location.Top);
        }

        public void ClearData()
        {
            gridControl1.Clear();
        }
        public void FillData(bool sortAsc, int startRow, Location location)
        {
            var windowSize = 100;
            using (var sqlCommand = new SqlCommand())
            {
                sqlCommand.Connection = _SqlConnection;
                sqlCommand.CommandText =
                    "SELECT rowNumber, fileName, dateModified FROM (SELECT ROW_NUMBER() OVER (ORDER BY dateModified " + (sortAsc ? "asc" : "desc") + ") as rowNumber, fileName, dateModified FROM directoryResults WHERE resultId = 1) T where T.rowNumber BETWEEN "+startRow+" and " + (startRow + windowSize - 1);
                DataTable results;
                using (var sqlDataAdapter = new SqlDataAdapter(sqlCommand))
                {
                    results = new DataTable();
                    sqlDataAdapter.Fill(results);
                }
                //gridControl1.Clear();
                switch (location)
                {

                    case OllieExplorer.Location.Bottom:

                        gridControl1.AddRows(results);
                        break;
                    case OllieExplorer.Location.Top:


                        gridControl1.AddTopRows(results);
                        break;
                    case OllieExplorer.Location.Clear:
                        gridControl1.Reset();
                        gridControl1.AddRows(results);
                        break;
                }
#if false
                foreach (DataRow row in results.Rows)
                {
                    var gridRow = new Dictionary<string, string>();
                    gridRow.Add("fileName", (string)row["fileName"]);
                    gridRow.Add("dateModified", Convert.ToString(row["dateModified"]));
                    gridControl1.AddRow(gridRow);
                }
#endif
            }
        }
        public Dictionary<string, string> GetRow(int row)
        {
            return gridControl1.GetRow(row);
        }
    }
}
